﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMTC.Model.Domain;
using System.Data.Common;
using IMPlant.Core.Data.Unity;
using System.Data;
using SMTC.Model.Model;

namespace SMTC.Dao.Report
{
    public class ReportDao : BaseDao
    {
        /// <summary>
        /// 获取BU的需求
        /// </summary>
        private const string SQL_SELECT_PLAN_BU = @"SELECT h.BusinessUnit BU
                                                  ,SUM(d.m1*b.UnitCount) m1,SUM(d.m2*b.UnitCount) m2,SUM(d.m3*b.UnitCount) m3,SUM(d.m4*b.UnitCount) m4
                                                  ,SUM(d.m5*b.UnitCount) m5,SUM(d.m6*b.UnitCount) m6,SUM(d.m7*b.UnitCount) m7,SUM(d.m8*b.UnitCount) m8
                                                  ,SUM(d.m9*b.UnitCount) m9,SUM(d.m10*b.UnitCount) m10,SUM(d.m11*b.UnitCount) m11,SUM(d.m12*b.UnitCount) m12
                                             FROM dbo.SMT_PlanHead h LEFT JOIN 
                                            (SELECT PlanID ID,MAX(Planyear) [year],MAX(xsvmateria) MaterialCode
                                                   ,SUM([1]) 'm1',SUM([2]) 'm2',SUM([3]) 'm3',SUM([4]) 'm4'
                                                   ,SUM([5]) 'm5',SUM([6]) 'm6',SUM([7]) 'm7',SUM([8]) 'm8'
                                                   ,SUM([9]) 'm9',SUM([10]) 'm10',SUM([11]) 'm11',SUM([12]) 'm12'
                                            FROM dbo.SMT_PlanDetail 
                                            PIVOT ( SUM(planmount) FOR planmonth IN (
                                             [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
                                            )
                                              ) AS pvt
                                            GROUP BY PlanID
                                            ) d ON h.PlanID = d.ID
                                            LEFT JOIN SMT_ProductBaseInfo b ON d.MaterialCode = b.ProductCode
                                            WHERE b.UnitCount IS NOT NULL 
                                            AND h.BusinessUnit like @buname
                                            and h.PlanVersion like @PlanVersion
                                            GROUP BY h.BusinessUnit";
        /// <summary>
        /// 获取套
        /// </summary>
        private const string SQL_SELECT_PLAN_BU_T = @"SELECT h.BusinessUnit BU
                                                  ,SUM(d.m1) m1,SUM(d.m2) m2,SUM(d.m3) m3,SUM(d.m4) m4
                                                  ,SUM(d.m5) m5,SUM(d.m6) m6,SUM(d.m7) m7,SUM(d.m8) m8
                                                  ,SUM(d.m9) m9,SUM(d.m10) m10,SUM(d.m11) m11,SUM(d.m12) m12
                                             FROM dbo.SMT_PlanHead h LEFT JOIN 
                                            (SELECT PlanID ID,MAX(Planyear) [year],MAX(xsvmateria) MaterialCode
                                                   ,SUM([1]) 'm1',SUM([2]) 'm2',SUM([3]) 'm3',SUM([4]) 'm4'
                                                   ,SUM([5]) 'm5',SUM([6]) 'm6',SUM([7]) 'm7',SUM([8]) 'm8'
                                                   ,SUM([9]) 'm9',SUM([10]) 'm10',SUM([11]) 'm11',SUM([12]) 'm12'
                                            FROM dbo.SMT_PlanDetail 
                                            PIVOT ( SUM(planmount) FOR planmonth IN (
                                             [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
                                            )
                                              ) AS pvt
                                            GROUP BY PlanID
                                            ) d ON h.PlanID = d.ID
                                            
                                            WHERE h.BusinessUnit like @buname
                                            and h.PlanVersion like @PlanVersion
                                            GROUP BY h.BusinessUnit";
        /// <summary>
        /// BU能力
        /// </summary>
        private const string SQL_SELECT_CAPACITY_BU = @"WITH targetCapacity AS
                                                    (
                                                        select  'm' + CONVERT(VARCHAR(3),MONTH(dateadd(dd,w.WorkNum,@yearStartDay))) [month]
                                                            ,SUM(dbo.CapacityDays(b.BUName,dateadd(dd,w.WorkNum,@yearStartDay),@PUVersion)) capacity
                                                            ,b.BUName bu
                                                        from  dbo.SMT_WorkNum w,dbo.SMT_BU b
                                                        where dateadd(dd,w.WorkNum,@yearStartDay)<=@YearEndDay
                                                        AND dateadd(dd,w.WorkNum,@yearStartDay) NOT IN 
                                                        (
                                                        SELECT HolidayDate FROM dbo.SMT_Holiday
                                                        )
                                                        AND b.BUName LIKE @bu
                                                        GROUP BY b.BUName,MONTH(dateadd(dd,w.WorkNum,@yearStartDay))
                                                    )
                                                    SELECT bu
                                                        ,SUM([m1]) 'm1',SUM([m2]) 'm2',SUM([m3]) 'm3',SUM([m4]) 'm4'
                                                        ,SUM([m5]) 'm5',SUM([m6]) 'm6',SUM([m7]) 'm7',SUM([m8]) 'm8'
                                                        ,SUM([m9]) 'm9',SUM([m10]) 'm10',SUM([m11]) 'm11',SUM([m12]) 'm12'
                                                    FROM targetCapacity AS c
                                                    PIVOT ( SUM(Capacity) FOR [month] IN (
                                                    [m1],[m2],[m3],[m4],[m5],[m6],[m7],[m8],[m9],[m10],[m11],[m12]
                                                    )
                                                    ) AS pvt
                                                    GROUP BY bu";
        /// <summary>
        /// 获取BU的月份详细需求
        /// </summary>
        /// <param name="BuName"></param>
        /// <returns></returns>
        public List<ReportDataSourceModel> GetReportPlan(string BuName, string PlanVersion)
        {
            DbParameter[] parameters = { 
                                           DBparameterBuild.Build("@buname", BuName),
                                           DBparameterBuild.Build("@PlanVersion",PlanVersion)
                                       };
            List<ReportDataSourceModel> resultList = new List<ReportDataSourceModel>();
            List<ImportPlanDomain> domainList = DBTemplete.ExcuteGetList<ImportPlanDomain>(CommandType.Text, SQL_SELECT_PLAN_BU, parameters);
            foreach (var item in domainList)
            {
                for (int index = 1; index <= 12; index++)
                {
                    ReportDataSourceModel ds = new ReportDataSourceModel();
                    ds.BU = item.BU;
                    ds.Capacity = this.GetMountByMonth(item, index);
                    ds.Year = index + "月";
                    resultList.Add(ds);
                }
            }

            return resultList;
        }

        /// <summary>
        /// 获取BU的月份详细需求
        /// </summary>
        /// <param name="BuName"></param>
        /// <returns></returns>
        public List<ImportPlanDomain> GetReportPlan(string PlanVersion)
        {
            DbParameter[] parameters = { 
                                           DBparameterBuild.Build("@buname", "%"),
                                           DBparameterBuild.Build("@PlanVersion", PlanVersion)
                                       };

            return  DBTemplete.ExcuteGetList<ImportPlanDomain>(CommandType.Text, SQL_SELECT_PLAN_BU, parameters);
            
        }

        /// <summary>
        /// 获取BU的月份详细需求
        /// </summary>
        /// <param name="BuName"></param>
        /// <returns></returns>
        public List<ImportPlanDomain> GetReportPlan_T(string PlanVersion)
        {
            DbParameter[] parameters = { 
                                           DBparameterBuild.Build("@buname", "%"),
                                           DBparameterBuild.Build("@PlanVersion", PlanVersion)
                                       };

            return DBTemplete.ExcuteGetList<ImportPlanDomain>(CommandType.Text, SQL_SELECT_PLAN_BU_T, parameters);

        }

        /// <summary>
        /// 获取BU的月份详细能力
        /// </summary>
        /// <param name="BuName"></param>
        /// <returns></returns>
        public List<ReportDataSourceModel> GetReportCapacity(string BuName, DateTime start, DateTime end, string PUVersion)
        {

            DbParameter[] parameters = { 
                                            DBparameterBuild.Build("@bu", BuName),
                                            DBparameterBuild.Build("@yearStartDay", start),
                                            DBparameterBuild.Build("@YearEndDay", end),
                                            DBparameterBuild.Build("@PUVersion", PUVersion)
                                       };
            List<ReportDataSourceModel> resultList = new List<ReportDataSourceModel>();
            List<ImportPlanDomain> domainList = DBTemplete.ExcuteGetList<ImportPlanDomain>(CommandType.Text, SQL_SELECT_CAPACITY_BU, parameters);
            foreach (var item in domainList)
            {
                for (int index = 1; index <= 12; index++)
                {
                    ReportDataSourceModel ds = new ReportDataSourceModel();
                    ds.BU = item.BU;
                    ds.Capacity = this.GetMountByMonth(item, index);
                    ds.Year = index + "月";
                    resultList.Add(ds);
                }
            }

            return resultList;
        }

        /// <summary>
        /// 获取BU的月份详细能力
        /// </summary>
        /// <param name="BuName"></param>
        /// <returns></returns>
        public List<ImportPlanDomain> GetReportCapacity(DateTime start, DateTime end, string PUVersion)
        {

            DbParameter[] parameters = { 
                                            DBparameterBuild.Build("@bu", "%"),
                                            DBparameterBuild.Build("@yearStartDay", start),
                                            DBparameterBuild.Build("@YearEndDay", end),
                                            DBparameterBuild.Build("@PUVersion", PUVersion)
                                       };
           return DBTemplete.ExcuteGetList<ImportPlanDomain>(CommandType.Text, SQL_SELECT_CAPACITY_BU, parameters);

        }

        /// <summary>
        /// 根据月份，获取该月份数据结构上的需求数据
        /// </summary>
        /// <param name="item"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public int GetMountByMonth(ImportPlanDomain item, int month)
        {
            switch (month)
            {
                case 1: return item.m1;
                case 2: return item.m2;
                case 3: return item.m3;
                case 4: return item.m4;
                case 5: return item.m5;
                case 6: return item.m6;
                case 7: return item.m7;
                case 8: return item.m8;
                case 9: return item.m9;
                case 10: return item.m10;
                case 11: return item.m11;
                case 12: return item.m12;
                default: throw new Exception("月份不存在");
            }
        }
    }
}
